/*

AUTHOR:				HAMID REZA SARMADI
Originally CREATED ON:		08 JAN 2008
Prepared to publish:		05 July 2011



	FUNCTIONALITY:
	HAS ONE OPTIONAL INPUT PARAMETER: 
	
	IF NOT GIVEN: FINDS ALL THE FILEDS (AND THEIR TABLES) WHICH CONFORM TO THE SEARCH STRING 
	IF GIVEN (THAT IS THE NAME OF TABLE): FINDS ALL THE FIELDS IN THE GIVEN TABLE WHICH CONFORM TO THE SEARCH STRING



*/


CREATE PROCEDURE DBO.COLUMNFINDER

@ColumnName AS VARCHAR(100),
@tableName AS VARCHAR(100)=NULL


AS

DECLARE @TMP_TABLE TABLE
	(

		ColumnName CHAR(200),
		TableName CHAR(100)

	)

IF @tableName IS NULL
BEGIN 
	
	DECLARE CURS1 CURSOR READ_ONLY FOR
	SELECT [name] FROM SYSOBJECTS WHERE TYPE = 'U'
	
	OPEN CURS1
	FETCH NEXT FROM CURS1 INTO @TableName
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			INSERT INTO @TMP_TABLE (ColumnName,TableName)
			SELECT name AS COLUMN_NAME, @TableName AS TABLE_NAME FROM SYSCOLUMNS
			WHERE OBJECT_NAME (id) = @TableName AND name LIKE '%' + @ColumnName + '%'
			ORDER BY colid
			
	
		FETCH NEXT FROM CURS1 INTO @TableName 
		END
	
	SELECT * FROM @TMP_TABLE ORDER BY ColumnName
	
	CLOSE CURS1
	DEALLOCATE CURS1
	
END
ELSE
BEGIN

	SELECT name AS COLUMN_NAME, @TableName AS TABLE_NAME FROM SYSCOLUMNS
	WHERE OBJECT_NAME (id) = @TableName AND name LIKE '%' + @ColumnName + '%'
	ORDER BY colid

END


GO
